create table [dbo].[k3r_newsletters] (
	[id]			[int] identity(1, 1) primary key,
	[email]			[nvarchar](250) not null,
	[subscribed]	[bit] not null default 0,
	[guid]			[uniqueidentifier] not null default newid(),
	[cd]			[datetime] not null default getdate(),
	[md]			[datetime] not null default getdate()
);

go

if exists (select *
			from [sys.objects]
			where [object_id] = object_id(n'[dbo].[k3r_newsletters_add]'
				and [type] in (n'P', n'PC'))
	drop proc [dbo].[k3r_newsletters_add];

go

create proc [dbo].[k3r_newsletters_add]
	@email				[nvarchar](250),
	@id					[int] = 0 output
as
insert into [k3r_newsletters] ([email])
values (@email);

select @id = scope_identity();

go

if exists (select *
			from [sys.objects]
			where [object_id] = object_id(n'[dbo].[k3r_newsletters_edit]'
				and [type] in (n'P', n'PC'))
	drop proc [dbo].[k3r_newsletters_edit];

go

create proc [dbo].[k3r_newsletters_edit]
	@id					[int],
	@email				[nvarchar](250),
	@subscribed			[bit]
as
update [k3r_newsletters]
set [email] = @email,
	[subscribed] = @subscribed,
	[md] = getdate()
where [id] = @id;

go

if exists (select *
			from [sys.objects]
			where [object_id] = object_id(n'[dbo].[k3r_newsletters_confirmGuid]'
				and [type] in (n'P', n'PC'))
	drop proc [dbo].[k3r_newsletters_confirmGuid];

go

create proc [dbo].[k3r_newsletters_confirmGuid]
	@guid				[uniqueidentifier]
as
update [k3r_newsletters]
set [subscribed] = 1
where [guid] = @guid;

go

if exists (select *
			from [sys.objects]
			where [object_id] = object_id(n'[dbo].[k3r_newsletters_load]'
				and [type] in (n'P', n'PC'))
	drop proc [dbo].[k3r_newsletters_load];

go

create proc [dbo].[k3r_newsletters_load]
	@id					[int] = null,
	@guid				[uniqueidentifier] = null
as
select n.[id]					,
		n.[email]				,
		n.[subscribed]			,
		n.[guid]			
from [k3r_newsletters] n
where n.[id] = isnull(@id, n.[id])
	and n.[guid] = isnull(@guid, n.[guid])

go

if exists (select *
			from [sys.objects]
			where [object_id] = object_id(n'[dbo].[k3r_newsletters_list]'
				and [type] in (n'P', n'PC'))
	drop proc [dbo].[k3r_newsletters_list];

go

create proc [dbo].[k3r_newsletters_list]
	@subscribed			[bit] = 1
as
select n.[id]					,
		n.[email]				,
		n.[subscribed]			,
		n.[guid]			
from [k3r_newsletters] n
where n.[subscribed] = @subscribed

go